<?php
	require("frmCookie.php");

	$iOpcion = $_GET[iOpcion];
	
	if(isset($_REQUEST['producto']))
	{
		$idProducto = $_REQUEST['producto'];
	}
	else
	{
		//Cuando entra desde el menu, no le pasa Producto en la URL por eso predetermino el Producto 2, que son los Celulares
		$idProducto = 2;	
	}
	
	if(isset($_POST['fecha']))
	{
		$fecha = $_POST['fecha'];
		
		$ano = substr($fecha,0,4);
		
		$sql = "select week('$fecha') as semana";
		$resultSemana = readSQL($sql);
		foreach($resultSemana as $valueSemana)
		{
			$semana = $valueSemana['semana'];
		}
		
		//Le sumo uno a la semana porque MySQL toma la semana 1 como semana 0
		$semana++;
	
		$mes = substr($fecha,5,2);
		
	}
	
	function returnMes($iMes)
	{
		switch($iMes)
		{
			case 1:
			{
				$sMes = "Enero";
				break;
			}	
			case 2:
			{
				$sMes = "Febrero";
				break;
			}
			case 3:
			{
				$sMes = "Marzo";
				break;
			}
			case 4:
			{
				$sMes = "Abril";
				break;
			}
			case 5:
			{
				$sMes = "Mayo";
				break;
			}
			case 6:
			{
				$sMes = "Junio";
				break;
			}
			case 7:
			{
				$sMes = "Julio";
				break;
			}
			case 8:
			{
				$sMes = "Agosto";
				break;
			}
			case 9:
			{
				$sMes = "Septiembre";
				break;
			}
			case 10:
			{
				$sMes = "Octubre";
				break;
			}
			case 11:
			{
				$sMes = "Noviembre";
				break;
			}
			case 12:
			{
				$sMes = "Diciembre";
				break;
			}
		}
		return $sMes;
	}
?>
<html>
	<head>
    	<?php
		require("frmHeader.php");
		?>
		<script language="javascript" src="scripts/ajax.js"></script>
		<script language="javascript" src="scripts/validar.js"></script>
		<script language="javascript">
		function validaForm()
		{
			if(document.getElementById("fecha").value == "")
			{
				alert("Por favor seleccione el primer lunes del mes o semana.");
				return false;
			}
			
			return true;
		}
	
		</script>
	</head>
	<body>
    	<table cellpadding="0" cellspacing="0" align="center" class="tablaPrincipal">
        	<tr>
            	<td align="center">
                	<?php
					require("frmSession.php"); 
					
					//Redirecciona a SINPERMISOS.PHP en caso de no tener permisos
					
					if (!(in_array(31000, $_SESSION[permisos])))
					{
					?>
					  <script language="JavaScript" type="text/javascript">
					  <!--
					  window.location.href = "sinPermisos.php"
					  //-->
					  </script>
					<?php
					}
					$titulo = "Reporte de Consumos";
					
					?>
                </td>
            </tr>
            <tr>
            	<td align="center">
                	<?php
					require("frmTitulo.php");
					?>
                </td>
            </tr>
            <tr>
            	<td height="20">&nbsp;
                	
                </td>
            </tr>
            <tr>
            	<td align="center">
                	<table cellpadding="0" cellspacing="0" align="center" class="menuPrincipal">
                    	<tr>
                        	<td>
								<?php
                                require("frmMenu.php");
                                ?>
                            </td>
                        </tr>
                    </table>
                </td>
            </tr>
            <tr>
            	<td height="20">&nbsp;
                	
                </td>
            </tr>
            <tr>
                <td align="center">
                	<table cellpadding="0" cellspacing="0" align="center" class="tituloPantalla">
                    	<tr>
                        	<td align="center">
                            	<p class="texto" align="left"><?php echo $titulo; ?></p>
                            </td>
                        </tr>
                    </table>
                </td>
            </tr>
             <tr>
            	<td height="20">&nbsp;
                	
                </td>
            </tr>
            <tr>
                <td align="center">
                	<table cellpadding="0" cellspacing="0" align="center" class="estadoPantalla">
                    	<tr>
                        	<td align="center">
                            	<p class="texto" align="left"><?php echo $mensaje; ?></p>
                            </td>
                        </tr>
                    </table>
                </td>
            </tr>
             <tr>
            	<td height="20">&nbsp;
                	
                </td>
            </tr>
            <tr>
                <td align="center">
                	<table cellpadding="0" cellspacing="0" align="center" class="contenedorPrincipal">
                    	<tr>
                        	<td align="">
                        		<?php
                        		switch($iOpcion)
                        		{
                        			case 1:
                        			{
                				?>
                				<form name="frm" action="consumosOK.php?iOpcion=2" onSubmit="return validaForm();" method="post">
                					<table>
                                    	<tr style="display:none;">
                                            <td>
                                                *Producto:
                                            </td>
                                            <td>
                                                <select name="producto" onChange="llenarComboModelos();">
                                                    <?php
                                                    
                                                    $mySQL = "Select * from productos order by nombre";
                                                    $productos = readSQL($mySQL);
                                                    foreach($productos as $producto)
                                                    {
                                                        if($producto['idProducto'] == $idProducto)
                                                        {
                                                            echo "<option value='".$producto['idProducto']."' selected>".$producto['nombre']."</option>";
                                                        }
                                                        else
                                                        {
                                                            echo "<option value='".$producto['idProducto']."'>".$producto['nombre']."</option>";
                                                            
                                                        }
                                                    }
                                                    
                                                    ?>
                                                </select>
                                            </td>
                                        </tr>
                						<tr>
                							<td>Tipo de Fecha: </td>
                							<td>
                								<select name="cboTipoConsumo">
                									<option value="1">Mensual</option>
                									<option value="2">Semanal</option>
                								</select>
                							</td>
                						</tr>
                						<tr>
                							<td>Marca: </td>
                							<td>
                								<select name="cboMarca">
                									<?php
				    							        $mySQL = "SELECT * FROM marcas";
												                    							
				    							        $result = readSQL($mySQL);
				    							
						  				                foreach($result as $value)
						  				                {
						  					                echo "<option value='" . $value[0] . "'>" . $value[1] . "</option>";
						  				                }
				    							      ?>
                								</select>
                							</td>
                						</tr>
                                        <tr>
                                            <td>
                                                Primer lunes del<br/>mes o semana:
                                            </td>
                                            <td>
                                                <input type="text" id="fecha" name="fecha" size="13" readonly="readonly">
                                            </td>
                                            <td>
                                                <img src="images/calendario.png" onClick="scwShow(fecha,event);" class="imagen" />
                                            </td>
                                        </tr>
                                        <!--
                						<tr>
                							<td>Mes: </td>
                							<td><input type="text" name="txtMes" size="2" maxlength="2" /></td>
                							<td>Ano: </td>
                							<td><input type="text" name="txtAnoMes" size="4" maxlength="4" /></td>
                						</tr>
                						<tr>
                							<td>Semana: </td>
                							<td><input type="text" name="txtSemana" size="2" maxlength="2" /></td>
                							<td>Ano: </td>
                							<td><input type="text" name="txtAnoSemana" size="4" maxlength="4" /></td>
                						</tr>
                                        -->
                						<tr>
                							<td colspan="2" align="center"><input type="submit" name="cmdEnviar" value="Generar" /></td>
                						</tr>
                					</table>
                				</form>
                				<?php
                        				break;
                        			}
                        			case 2:
                        			{	                        				                        				
		                        		$sucursalesNombres = array();
		                        		$sucursalesIds = array();
		                        		
		                        		$mySQL = "SELECT * FROM sucursales";
		                        		$result = readSQL($mySQL);
		                        		$cantidadSucursales = 0;
										foreach($result as $value)
										{
											array_push($sucursalesNombres, $value['nombre']);
											array_push($sucursalesIds, $value['idSucursal']);
											$cantidadSucursales++;	
										}
		                        		
		                        		$mySQL = "SELECT MC.nombre AS MARCA, ";
		                        		$mySQL = $mySQL . "P.idParte, ";
		                        		$mySQL = $mySQL . "P.codigoTango, ";
		                        		$mySQL = $mySQL . "TP.nombre AS TIPOPARTE, ";
		                        		$mySQL = $mySQL . "P.descripcionFabricante, ";
		                        		$mySQL = $mySQL . "P.descripcionInterna, ";
		                        		$mySQL = $mySQL . "P.codigoFabricante, ";
		                        		$mySQL = $mySQL . "P.codigoDistribuidor, ";
		                        		$mySQL = $mySQL . "(SELECT COALESCE(MAX(precio), 0) FROM stock WHERE idParte = P.idParte) AS COSTO ";
		                        		$mySQL = $mySQL . "FROM partes P ";
										$mySQL = $mySQL . "LEFT JOIN tipospartes TP ON TP.idTipoParte = P.idTipoParte ";
										$mySQL = $mySQL . "LEFT JOIN partesxmodelo PXM ON PXM.idParte = P.idParte ";
										$mySQL = $mySQL . "LEFT JOIN modelos M ON PXM.idModelo = M.idModelo ";
										$mySQL = $mySQL . "LEFT JOIN marcas MC ON MC.idMarca = M.idMarca ";
										$mySQL = $mySQL . " WHERE P.descripcionInterna <> 'N.U.P' ";
										$mySQL = $mySQL . "AND MC.idMarca = " . $_POST['cboMarca'] . " GROUP BY P.codigoTango";
										
										//echo $mySQL;
										//exit;

		                        		$superHead = array();
		                        		array_push($superHead, "");
		                        		array_push($superHead, "");
		                        		array_push($superHead, "");
		                        		array_push($superHead, "");
		                        		array_push($superHead, "");
		                        		array_push($superHead, "");
		                        		array_push($superHead, "");
		                        		array_push($superHead, "");
		                        		array_push($superHead, "");
		                        		array_push($superHead, "");
		                        		array_push($superHead, "");
		                        		for($i = 0; $i<$cantidadSucursales; $i++)
                        				{
                        					array_push($superHead, $sucursalesNombres[$i]);
                        					array_push($superHead, "");
		                        			array_push($superHead, "");
		                        			array_push($superHead, "No consumo");
		                        			array_push($superHead, "No consumo");
											array_push($superHead, "No consumo");
		                        			array_push($superHead, "");
		                        			array_push($superHead, "");
                        				}
		                        		
		                        		
		                        		$data = array();
		                        		
		                        		array_push($data, $superHead);
		                        			
		                        		$head = array();
		                        		array_push($head, "Destino");
		                        		array_push($head, "Marca");
		                        		array_push($head, "Codigo Tango");
		                        		array_push($head, "Tipo Parte");
		                        		array_push($head, "Descripcion Fabricante");
		                        		array_push($head, "Descripcion Interna");
		                        		array_push($head, "Modelos");
		                        		array_push($head, "Codigo Fabricante");
		                        		array_push($head, "Codigo Distribuidor");
		                        		array_push($head, "Referencia");
		                        		array_push($head, "Costo");
		                        		
		                        		if($_POST['cboTipoConsumo'] == 1)
		                        		{
		                        			//$mes = $_POST['txtMes'];
		                        			//$ano = $_POST['txtAnoMes'];
		                        			
		                        			if(($mes-1) == 0)
		                        			{
		                        				$meses = "(" . $mes . ", 12, 11)";
		                        				$anos = "(" . ($ano-1) . "," . $ano . ")";
		                        				for($i = 0; $i<$cantidadSucursales; $i++)
		                        				{
		                        					array_push($head, "Noviembre");
		                        					array_push($head, "Diciembre");
		                        					array_push($head, "Enero");
													array_push($head, "Noviembre");
		                        					array_push($head, "Diciembre");
		                        					array_push($head, "Enero");
		                        					array_push($head, "Consumo Fijado");
		                        					array_push($head, "Stock");
		                        				}
		                        				$mes1 = "11";
	                        					$ano1 = ($ano-1);	
	                        					$mes2 = "12";
	                        					$ano2 = ($ano-1);
	                        					$mes3 = "1";
	                        					$ano3 = $ano;
		                        			}
		                        			else
		                        			{
		                        				if(($mes-1) == 1)
		                        				{
		                        					$meses = "(" . $mes . ", 1, 12)";
		                        					$anos = "(" . ($ano-1) . "," . $ano . ")";
		                        					for($i = 0; $i<$cantidadSucursales; $i++)
		                        					{
		                        						array_push($head, "Diciembre");
		                        						array_push($head, "Enero");
		                        						array_push($head, "Febrero");
														array_push($head, "Diciembre");
		                        						array_push($head, "Enero");
		                        						array_push($head, "Febrero");
		                        						array_push($head, "Consumo Fijado");
		                        						array_push($head, "Stock");
		                        					}
		                        					$mes1 = "12";	
	                        						$ano1 = ($ano-1);
	                        						$mes2 = "1";
	                        						$ano2 = $ano;
	                        						$mes3 = "2";
	                        						$ano3 = $ano;	
		                        				}
		                        				else
		                        				{
		                        					$meses = "(" . ($mes) . "," . ($mes-1) . ", " . ($mes-2) . ")";
		                        					$anos = "(" . $ano . ")";
		                        					for($i = 0; $i<$cantidadSucursales; $i++)
		                        					{
		                        						array_push($head, returnMes(($mes-2)));
		                        						array_push($head, returnMes(($mes-1)));
		                        						array_push($head, returnMes($mes));
														array_push($head, returnMes(($mes-2)));
		                        						array_push($head, returnMes(($mes-1)));
		                        						array_push($head, returnMes($mes));
		                        						array_push($head, "Consumo Fijado");
		                        						array_push($head, "Stock");
		                        					}
		                        					$mes1 = $mes;
	                        						$ano1 = $ano;	
	                        						$mes2 = ($mes-1);
	                        						$ano2 = $ano;
	                        						$mes3 = ($mes-2);
	                        						$ano3 = $ano;
		                        				}
		                        			}
		                        			//$mySQL = $mySQL . "AND P.idParte IN(SELECT idParte FROM consumos WHERE mes IN" . $mes . " AND ano IN" . $ano . ") GROUP BY P.codigoTango";	
		                        		}
		                        		else
		                        		{
		                        			//$semana = $_POST['txtSemana'];
		                        			//$ano = $_POST['txtAnoSemana'];
		                        			
		                        			if(($semana-1) == 0)
		                        			{
		                        				$semanas = "(" . $semana . ", 52, 51)";
		                        				$anos = "(" . ($ano-1) . "," . $ano . ")";
		                        				for($i = 0; $i<$cantidadSucursales; $i++)
		                        				{
		                        					array_push($head, "51");
		                        					array_push($head, "52");
		                        					array_push($head, "1");
													array_push($head, "51");
		                        					array_push($head, "52");
		                        					array_push($head, "1");
		                        					array_push($head, "Consumo Fijado");
		                        					array_push($head, "Stock");
		                        				}
		                        				$semana1 = "51";
	                        					$ano1 = ($ano-1);	
	                        					$semana2 = "52";
	                        					$ano2 = ($ano-1);
	                        					$semana3 = "1";
	                        					$ano3 = $ano;
		                        			}
		                        			else
		                        			{
		                        				if(($semana-1) == 1)
		                        				{
		                        					$semanas = "(" . $semana . ", 1, 52)";
		                        					$anos = "(" . ($ano-1) . "," . $ano . ")";
		                        					for($i = 0; $i<$cantidadSucursales; $i++)
			                        				{
			                        					array_push($head, "52");
			                        					array_push($head, "1");
			                        					array_push($head, "2");
														array_push($head, "52");
			                        					array_push($head, "1");
			                        					array_push($head, "2");
			                        					array_push($head, "Consumo Fijado");
			                        					array_push($head, "Stock");
			                        				}
		                        					$semana1 = "52";	
	                        						$ano1 = ($ano-1);
	                        						$semana2 = "1";
	                        						$ano2 = ($ano);
	                        						$semana3 = "2";
	                        						$ano3 = ($ano);
		                        				}
		                        				else
		                        				{
		                        					$semanas = "(" . ($semana) . "," . ($semana-1) . ", " . ($semana-2) . ")";
		                        					$anos = "(" . $ano . ")";
		                        					for($i = 0; $i<$cantidadSucursales; $i++)
			                        				{
			                        					array_push($head, ($semana-2));
			                        					array_push($head, ($semana-1));
			                        					array_push($head, $semana);
														array_push($head, ($semana-2));
			                        					array_push($head, ($semana-1));
			                        					array_push($head, $semana);
			                        					array_push($head, "Consumo Fijado");
			                        					array_push($head, "Stock");
			                        				}
		                        					$semana1 = $semana;
	                        						$ano1 = $ano;	
	                        						$semana2 = ($semana-1);
	                        						$ano2 = $ano;
	                        						$semana3 = ($semana-2);
	                        						$ano3 = $ano;
		                        				}
		                        			}
		                        			//$mySQL = $mySQL . "AND P.idParte IN(SELECT idParte FROM consumos WHERE semana IN" . $semana . " AND ano IN" . $ano . ")  GROUP BY P.codigoTango";	
		                        		}
		                        		
		                        		array_push($head, "Consumo Promedio Mensual");
		                        		
		                        		array_push($data, $head);
		                        		
		                        		$semana1--;
										$semana2--;
										$semana3--;

		                        		$result = readSQL($mySQL);
										foreach($result as $value)
										{
											//echo "ENTRO";
											$registro = array();
											
											// DETERMINO LOS TIPOS DE USOS PARA LA PARTE EN CUESTION
											$mySQL = "SELECT TU.nombre ";
											$mySQL = $mySQL . "FROM tiposusos TU ";
											$mySQL = $mySQL . "WHERE TU.idTipoUso IN(SELECT TUXS.idTipoUso FROM tiposusosxstock TUXS WHERE TUXS.idParte = " . $value['idParte'] . ")";
											
											$usos = "";
											$cantUsos = 0;
											$resultUsos = readSQL($mySQL);
											foreach($resultUsos as $valueUsos)
											{
												if($cantUsos == 0)
													$usos = $valueUsos['nombre'];
												else
													$usos = $usos . " - " . $valueUsos['nombre'];
													
												$cantUsos++;
											}
											
											// DETERMINO LOS MODELOS ASOCIADOS A LA PARTE EN CUESTION
											$mySQL = "SELECT M.nombreComercial ";
											$mySQL = $mySQL . "FROM modelos M, partesxmodelo PXM ";
											$mySQL = $mySQL . "WHERE M.idModelo = PXM.idModelo AND PXM.idParte = " . $value['idParte'];
											
											$modelos = "";
											$cantModelos = 0;
											$resultModelos = readSQL($mySQL);
											foreach($resultModelos as $valueModelos)
											{
												if($cantModelos == 0)
													$modelos = $valueModelos['nombreComercial'];
												else
													$modelos = $modelos . " - " . $valueModelos['nombreComercial'];
													
												$cantModelos++;
											}
																						
											//array_push($registro, strtoupper($valueOrden['idOrden']));
											array_push($registro, $usos);
											array_push($registro, $value['MARCA']);
											array_push($registro, $value['codigoTango']);
											array_push($registro, $value['TIPOPARTE']);
											array_push($registro, $value['descripcionFabricante']);
											array_push($registro, $value['descripcionInterna']);
											array_push($registro, $modelos);
											array_push($registro, $value['codigoFabricante']);
											array_push($registro, $value['codigoDistribuidor']);
											array_push($registro, "");
											array_push($registro, $value['COSTO']);
											
											if($_POST['cboTipoConsumo'] == 1)
		                        			{
		                        				for($i = 0; $i<$cantidadSucursales; $i++)
	                        					{
	                        						$mySQL = "SELECT P.idParte, ";
													$mySQL = $mySQL . "(SELECT SUM(C.cantidad) FROM consumos C WHERE C.idParte=" . $value['idParte'] . " AND C.mes=" . $mes1 . " AND C.ano=" . $ano1 . " AND C.idSucursal=" . $sucursalesIds[$i] . "), ";
													$mySQL = $mySQL . "(SELECT SUM(C.cantidad) FROM consumos C WHERE C.idParte=" . $value['idParte'] . " AND C.mes=" . $mes2 . " AND C.ano=" . $ano2 . " AND C.idSucursal=" . $sucursalesIds[$i] . "), ";
													$mySQL = $mySQL . "(SELECT SUM(C.cantidad) FROM consumos C WHERE C.idParte=" . $value['idParte'] . " AND C.mes=" . $mes3 . " AND C.ano=" . $ano3 . " AND C.idSucursal=" . $sucursalesIds[$i] . "), ";
													$mySQL = $mySQL . "(SELECT SUM(C.cantidad) FROM noconsumos C WHERE C.idParte=" . $value['idParte'] . " AND C.mes=" . $mes1 . " AND C.ano=" . $ano1 . " AND C.idSucursal=" . $sucursalesIds[$i] . "), ";
													$mySQL = $mySQL . "(SELECT SUM(C.cantidad) FROM noconsumos C WHERE C.idParte=" . $value['idParte'] . " AND C.mes=" . $mes2 . " AND C.ano=" . $ano2 . " AND C.idSucursal=" . $sucursalesIds[$i] . "), ";
													$mySQL = $mySQL . "(SELECT SUM(C.cantidad) FROM noconsumos C WHERE C.idParte=" . $value['idParte'] . " AND C.mes=" . $mes3 . " AND C.ano=" . $ano3 . " AND C.idSucursal=" . $sucursalesIds[$i] . "), ";
													$mySQL = $mySQL . "(SELECT sum(cantidad) as cantidad FROM fijacionstock FS WHERE FS.idParte=" . $value['idParte'] . " AND FS.idSucursal=" . $sucursalesIds[$i] . "), ";
													$mySQL = $mySQL . "(SELECT cantidad FROM stockxdia S WHERE S.idParte = " . $value['idParte'] . " AND S.idSucursal=" . $sucursalesIds[$i] . " AND S.mes = " . $mes . " AND S.ano=" . $ano . " AND S.dia = (SELECT MAX(SD.dia) FROM stockxdia SD WHERE SD.ano=" . $ano . " AND SD.mes=" . $mes . ")) ";
													$mySQL = $mySQL . "FROM partes P WHERE P.idParte=" . $value['idParte'];
													//echo $mySQL . "<br>";
													//exit;
													$resultConsumos = readSQL($mySQL);
													foreach($resultConsumos as $valueConsumos)
													{ 
														array_push($registro, $valueConsumos[3]);
														array_push($registro, $valueConsumos[2]);
														array_push($registro, $valueConsumos[1]);
														array_push($registro, $valueConsumos[6]);
														array_push($registro, $valueConsumos[5]);
														array_push($registro, $valueConsumos[4]);
														array_push($registro, $valueConsumos[7]);
														array_push($registro, $valueConsumos[8]);
														
													}
	                        					}
	                        					
	                        					$mySQL = "SELECT SUM(cantidad)/3 FROM consumos WHERE mes IN " . $meses . " AND ano IN " . $anos . " AND idParte=" . $value['idParte'];
	                        					$resultConsumoProm = readSQL($mySQL);
												foreach($resultConsumoProm as $valueConsumoProm)
												{ 
													array_push($registro, $valueConsumoProm[0]);
												}
		                        			}
		                        			else
		                        			{
		                        				for($i = 0; $i<$cantidadSucursales; $i++)
	                        					{
	                        						$mySQL = "SELECT P.idParte, ";
													$mySQL = $mySQL . "(SELECT SUM(C.cantidad) FROM consumos C WHERE C.idParte=" . $value['idParte'] . " AND C.semana=" . $semana1 . " AND C.ano=" . $ano1 . " AND C.idSucursal=" . $sucursalesIds[$i] . "), ";
													$mySQL = $mySQL . "(SELECT SUM(C.cantidad) FROM consumos C WHERE C.idParte=" . $value['idParte'] . " AND C.semana=" . $semana2 . " AND C.ano=" . $ano2 . " AND C.idSucursal=" . $sucursalesIds[$i] . "), ";
													$mySQL = $mySQL . "(SELECT SUM(C.cantidad) FROM consumos C WHERE C.idParte=" . $value['idParte'] . " AND C.semana=" . $semana3 . " AND C.ano=" . $ano3 . " AND C.idSucursal=" . $sucursalesIds[$i] . "), ";
													$mySQL = $mySQL . "(SELECT SUM(C.cantidad) FROM noconsumos C WHERE C.idParte=" . $value['idParte'] . " AND C.semana=" . $semana1 . " AND C.ano=" . $ano1 . " AND C.idSucursal=" . $sucursalesIds[$i] . "), ";
													$mySQL = $mySQL . "(SELECT SUM(C.cantidad) FROM noconsumos C WHERE C.idParte=" . $value['idParte'] . " AND C.semana=" . $semana2 . " AND C.ano=" . $ano2 . " AND C.idSucursal=" . $sucursalesIds[$i] . "), ";
													$mySQL = $mySQL . "(SELECT SUM(C.cantidad) FROM noconsumos C WHERE C.idParte=" . $value['idParte'] . " AND C.semana=" . $semana3 . " AND C.ano=" . $ano3 . " AND C.idSucursal=" . $sucursalesIds[$i] . "), ";
													$mySQL = $mySQL . "(SELECT sum(cantidad) as cantidad FROM fijacionstock FS WHERE FS.idParte=" . $value['idParte'] . " AND FS.idSucursal=" . $sucursalesIds[$i] . "), ";
													$mySQL = $mySQL . "(SELECT cantidad FROM stockxdia S WHERE S.idParte = " . $value['idParte'] . " AND S.idSucursal=" . $sucursalesIds[$i] . " AND S.semana = " . $semana . " AND S.ano=" . $ano . " AND S.dia = (SELECT MAX(SD.dia) FROM stockxdia SD WHERE SD.ano=" . $ano . " AND SD.semana=" . $semana . ")) ";
													$mySQL = $mySQL . "FROM partes P WHERE P.idParte=" . $value['idParte'];
													//echo $mySQL . "<br>";
													//exit;
													$resultConsumos = readSQL($mySQL);
													foreach($resultConsumos as $valueConsumos)
													{ 
														array_push($registro, $valueConsumos[3]);
														array_push($registro, $valueConsumos[2]);
														array_push($registro, $valueConsumos[1]);
														array_push($registro, $valueConsumos[6]);
														array_push($registro, $valueConsumos[5]);
														array_push($registro, $valueConsumos[4]);
														array_push($registro, $valueConsumos[7]);
														array_push($registro, $valueConsumos[8]);
													}
	                        					}
	                        					
	                        					$mySQL = "SELECT SUM(cantidad)/3 FROM consumos WHERE semana IN " . $semanas . " AND ano IN " . $anos . " AND idParte=" . $value['idParte'];
	                        					$resultConsumoProm = readSQL($mySQL);
												foreach($resultConsumoProm as $valueConsumoProm)
												{ 
													array_push($registro, $valueConsumoProm[0]);
												}	
		                        			}
											
											array_push($data, $registro);
										}
		                        		
		                        		// include package
										require 'Spreadsheet/Excel/Writer.php';
										
										// Fecha Hora Inicio
										$fecha = getdate();
										$dia = $fecha[mday];
										$mes = $fecha[mon];
										$ano = $fecha[year];
										$hora = $fecha[hours];
										$minutos = $fecha[minutes];
										$segundos = $fecha[seconds];
										$archivo =  $ano . "-" . $mes . "-" . $dia . "-" . $hora . "-" . $minutos . "-" . $segundos . "Consumos.xls";
										
										// create empty file
										$excel = new Spreadsheet_Excel_Writer("/var/www/html/galander/informes/" . $archivo);
										//$excel = new Spreadsheet_Excel_Writer("informes/" . $archivo);
										
										// add worksheet
										$sheet =& $excel->addWorksheet('Hoja 1');
										
										// create format for header row 
										// bold, red with black lower border
										/*$firstRow =& $excel->addFormat();
										$firstRow->setBold();
										$firstRow->setColor('black');
										$firstRow->setBottom(1);*/
										//$firstRow->setBottomColor('black');
										
										// create format for header column
										// blue with black right border
										/*$firstCol =& $excel->addFormat();
										$firstRow->setBold();
										$firstCol->setColor('black');*/
										//$firstCol->setRight(1);
										//$firstCol->setRightColor('black');
										
										// create format for top left corner
										/*$firstRowCol =& $excel->addFormat();
										$firstRowCol->setBottom(1);*/
										//$firstRowCol->setRight(1);
										//$firstRowCol->setBorderColor('black');
										
										// create format for numeric cells
										
										$codigoTango =& $excel->addFormat();
										$codigoTango->setNumFormat('000000000000');
										
										/*$num =& $excel->addFormat();
										$num->setNumFormat('#');
										
										
										$prodcode =& $excel->addFormat();
										$prodcode->setNumFormat('0000000');
										
										$fecIng =& $excel->addFormat();
										$fecIng->setNumFormat('mm-yyyy-dd');
										
										$modelo =& $excel->addFormat();
										$modelo->setAlign('left');*/
										
										// create format for averaged values
										/*$avg =& $excel->addFormat();
										$avg->setNumFormat('0.00');*/
										
										// add data to worksheet
										$rowCount = 0;
										
										foreach ($data as $row) 
										{
											for($colCount = 0; $colCount < sizeof($row); $colCount++) 
											{
											    switch($colCount)
											    {
											    	case 2:
											    	{
											    		$format = 'codigoTango';
											    		break;
											    	}
											    	default:
											    	{
											    		$format = "";
											    		break;
											    	}
											    }
											    /*if ($rowCount == 0 && $colCount == 0) {
											      $format = 'firstRowCol';
											    } else if ($rowCount == 0) {
											        $format = 'firstRow';        
											    } else if ($colCount == 0) {
											        $format = 'firstCol';                
											    } else {
											        $format = 'num';                      
											    }*/
											    /*if($colCount == 11)
											    	$format = 'imeiF';
											    else if($colCount == 15)
											    	$format = 'prodcode';
											    else if($colCount == 19)
											    	$format = 'modelo';
											    else
											    	$format = 'num';*/
											    /*if($colCount == 4 || $colCount == 8 || $colCount == 9)
											    {
											    	$format = 'num';
											    }*/
										    	
										    	if($format != "")
										    		$sheet->write($rowCount, $colCount, $row[$colCount], $$format);
										    	else
										    		$sheet->write($rowCount, $colCount, $row[$colCount]);
										    		
									    		//$sheet->mergeCells(0, 11, 0, 14);
									    		
									    		/*$j = 11;
		                        				$h = 15;
									    		for($i = 1; $i<$cantidadSucursales; $i++)
		                        				{
		                        					$sheet->mergeCells(0, $j, 0, $h);
		                        					$j+=5;
		                        					$h+=5;
		                        				}*/
										    	
										   		//$sheet->write($rowCount, $colCount, $row[$colCount], $$format);                       
										   		//$sheet->write($rowCount, $colCount, $row[$colCount]);
										  	}  
										  // get cell coordinates
										  $start = Spreadsheet_Excel_Writer::rowcolToCell($rowCount, 1);
										  $end = Spreadsheet_Excel_Writer::rowcolToCell($rowCount, (sizeof($row)-1));
										  // add average() formula to terminating cell of each row
										  // except the first (header) row
										  /*if ($rowCount != 0) {
										    $sheet->writeFormula($rowCount, sizeof($row), "=AVERAGE($start:$end)", $avg);     
										  } */
										  $rowCount++;
										}
										
										// save file to disk
										if ($excel->close() === true) {
										  //echo 'Spreadsheet successfully saved!';
									      echo "<a class='linknuevo' href='/galander/informes/" . $archivo . "'>Descargar Archivo</a>";  
										} else {
										  echo 'ERROR: Could not save spreadsheet.';
										}
										
										/*foreach($data as $val)
										{
											foreach($val as $val1)
											{
												echo $val1 . "<br>";
											}
										}*/		
                        				break;
                        			}
                        		}
                        		?>
                            </td>
                        </tr>
                    </table>
                </td>
            </tr>
        </table>
	</body>
</html>